﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BOM.entity;

namespace BOM.dao
{
    class PriceBillNew
    {

        //向表priceBill中新增数据
        public static bool InsertPriceBill()
        {
            PriceBillsurface priceBill = new PriceBillsurface();
            string Str = "'"+priceBill.Fnumber+"','"+priceBill.Fname+"','"+priceBill.Fcreatetime+"',";
            Str += "'" + priceBill.Fmodel + "'," + priceBill.Fcustomerid + ",'" + priceBill.Fsize + "','" + priceBill.Funit + "',";
            Str += "" + priceBill.Farea + "," + priceBill.Fperimeter + "," + priceBill.Fdifficulty + "," + priceBill.FmanageFee + "," + priceBill.FmadeFee + ",";
            Str += "" + priceBill.FmaterialFee + "," + priceBill.FtotalCost + ",'"+priceBill.Fface+"'";
            string cmdText = "insert into priceBill (fnumber,fname,fcreatetime,fmodel,fcustomerid,fsize,funit,farea,fperimeter,fdifficulty,fmanageFee,fmadeFee,fmaterialFee,ftotalCost,fface) values (" + Str + ")";
            int i = DbService.executeSQL(cmdText);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据fid查询表priceBill数据
        public static DataTable SelectReasonFid(int fid)
        {
            string cmdText = "select * from priceBill where fid = " + fid + "";
            DataTable table = DbService.getSQLTable(cmdText,"priceBill");
            if (table.Rows.Count == 1)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据fid删除表priceBill数据
        public static bool DeleteFidPriceBill(int fid)
        {
            PriceBillsurface priceBill = new PriceBillsurface();
            string cmdText = "delete from priceBill where fid = " + fid + "";
            int i = DbService.executeSQL(cmdText);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //查询表materialgroup和表material的所有数据
        public static DataTable SelectMaterialgroupAndMaterial()
        {
            string cmdText = "select material.fid as materialfid,materialgroup.fname,material.fname,fsize as materialfsize,funit as materialfunit,fprice as materialfprice from material,materialgroup where materialgroup.fid = material.fgroupid";
            DataTable table = DbService.getSQLTable(cmdText, "materialgroupAndmaterial");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //向表priceBillEntry新增数据
        public static bool PriceBillEntryInsert()
        {
            PriceBillEntry priceBillentry = new PriceBillEntry();
            string cmdText = "";
            if (priceBillentry.FisDetail)
            {
                string str = "" + priceBillentry.Fparentid + "," + priceBillentry.Fmaterialid + ",";
                str += "'" + priceBillentry.Fsize + "','" + priceBillentry.Funit + "'," + priceBillentry.Fprice + "," + priceBillentry.Fqty + ",";
                str += "" + priceBillentry.Famount + ",'" + priceBillentry.Fdescription + "','" + priceBillentry.Fseq + "','" + priceBillentry.FparentSeq + "'," + priceBillentry.FisDetail + ","+priceBillentry.FmaterialGroupId;
                cmdText = "insert into PriceBillEntry (fparentid,fmaterialid,fsize,funit,fprice,fqty,famount,fdescription,fseq,fparentSeq,fisDetail,fmaterialGroupId) values (" + str + ")";
            }
            else
            {
                string str = ""+priceBillentry.Fparentid +","+priceBillentry.FmaterialGroupId+","+priceBillentry.Famount+","+priceBillentry.Fseq+","+priceBillentry.FparentSeq+","+priceBillentry.FisDetail;
                cmdText = "insert into PriceBillEntry(fparentid,fmaterialGroupId,famount,fseq,fparentSeq,fisDetail) values ("+str+")";
            }
            int i = DbService.executeSQL(cmdText);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据流水序号查询priceBill中的数据
        public static DataTable SelectFnumberPriceBill(string fnumber)
        {
            string cmdText = "select fid from priceBill where fnumber = '" + fnumber + "'";
            DataTable table = DbService.getSQLTable(cmdText,"priceBill");
            if (table.Rows.Count == 1)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //查询PriceBill表中的所有信息
        public static DataTable SelectPriceBill()
        {
            string cmdText = "select PriceBill.*,customer.* from PriceBill,customer where customer.fid = fcustomerid order by PriceBill.fid desc";
            DataTable table = DbService.getSQLTable(cmdText, "PriceBill");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        ////根据 fparentid 查询priceBillEntry表中信息
        //public static DataTable SelectPriceBillEntry(int fparentid)
        //{
        //    string cmdText = "select * from priceBillEntry where fparentid = " + fparentid + "";
        //    DataTable table = DbService.getSQLTable(cmdText, "priceBillEntry");
        //    if (table.Rows.Count > 0)
        //    {
        //        return table;
        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}
        //根据fname字查客户fid 在表 customer中
        public static DataTable SelectFnameFid(string fname)
        {
            string cmdText = "select fid from customer where fname = '" + fname + "'";
            DataTable table = DbService.getSQLTable(cmdText, "customer");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据客户的fid查客户的fname 在表 customer中
        public static string SelectFidFname(int fid)
        {
            string cmdText = "select * from customer where fid = " + fid + "";
            DataTable table = DbService.getSQLTable(cmdText, "customer");
            if (table.Rows.Count == 1)
            {
                string fname = table.Rows[0]["fname"].ToString();
                return fname;
            }
            else
            {
                return null;
            }

        }
        //根据计价单id查询表priceBillEntry和表material和表materialg的数据
        public static DataTable SelectPriceBillEntryAndMaterial(int fid)
        {
            //string cmdText = "select material.fid as materialfid,materialgroup.fname,material.fname,material.fsize as materialfsize,material.funit as materialfunit,material.fprice as materialfprice,material.*,priceBillEntry.*,materialgroup.* from priceBillEntry,material,materialgroup where material.fid = priceBillEntry.fmaterialid and materialgroup.fid = material.fgroupid and priceBillEntry.fparentid = " + fid + "";
            string cmdText = "select materialgroup.fid as materialGroupId,materialgroup.fname,material.fid as materialid,material.fid as materialfid,material.fname,material.fsize as materialfsize,material.funit as materialfunit,priceBillEntry.fprice as materialfprice,material.*,priceBillEntry.*,materialgroup.* "
                            +" from (priceBillEntry left join material on material.fid = priceBillEntry.fmaterialid ) "
                            + " left join materialgroup  on  materialgroup.fid = priceBillEntry.fmaterialGroupId "
                            +" where  priceBillEntry.fparentid = "+fid;
            DataTable table = DbService.getSQLTable(cmdText, "PriceBillEntryAndMaterial");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据ID修改表priceBill中数据
        public static bool UpdatePriceBill(int fid)
        {
            PriceBillsurface priceBill = new PriceBillsurface();
            string cmdText = "update priceBill set fnumber = '" + priceBill.Fnumber + "',fname = '" + priceBill.Fname + "',fcreatetime = '" + priceBill.Fcreatetime + "',";
            cmdText += "fmodel = '" + priceBill.Fmodel + "',fcustomerid = " + priceBill.Fcustomerid + ",fsize = '" + priceBill.Fsize + "',funit = '" + priceBill.Funit + "',";
            cmdText += "farea = " + priceBill.Farea + ",fperimeter = " + priceBill.Fperimeter + ",fdifficulty = " + priceBill.Fdifficulty + ",fmanageFee = " + priceBill.FmanageFee + ",fmadeFee = " + priceBill.FmadeFee + ",";
            cmdText += "fmaterialFee = " + priceBill.FmaterialFee + ",ftotalCost = " + priceBill.FtotalCost + ",fface='"+priceBill.Fface+"' where fid = " + fid + "";
            int i = DbService.executeSQL(cmdText);
            if (i == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据fparentid修改表priceBillEntry中的数据
        public static bool UpdatePriceBillEntry(int fid)
        {
            PriceBillEntry priceBillentry = new PriceBillEntry();
            string cmdText = "update priceBill set fparentid = " + priceBillentry.Fparentid + ",fmaterialid = " + priceBillentry.Fmaterialid + ",";
            cmdText += "fsize = '" + priceBillentry.Fsize + "',funit = '" + priceBillentry.Funit + "',fprice = " + priceBillentry.Fprice + ",fqty = " + priceBillentry.Fqty + ",";
            cmdText += "famount = " + priceBillentry.Famount + ",fdescription = '" + priceBillentry.Fdescription + "' where fparentid = " + fid + "";
            int i = DbService.executeSQL(cmdText);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据fparentid删除表priceBillEntry中的数据
        public static bool DeletePriceBillEntry(int fparentid)
        {
            string cmdText = "delete from priceBillEntry where fparentid = " + fparentid + "";
            int i = DbService.executeSQL(cmdText);
            if (i >= 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据序号,名称，型号模糊搜索表priceBill内容
        public static DataTable SelectPriceBillNberNme(string str)
        {
            string cmdTxt = "select * from priceBill where fnumber like '%" + str + "%' or fname like '%" + str + "%' or fcustomerid like '%" + str + "%'";
            DataTable table = DbService.getSQLTable(cmdTxt,"priceBill");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }

        }
        //根据ID删除表priceBillEntry中的信息
        public static bool DeletePriceBillEntryFid(int fid)
        {
            string cmdText = "delete from priceBillEntry where fid = " + fid + "";
            int i = DbService.executeSQL(cmdText);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //根据流水号 查询 ID
        public static int SelectPriceBillFnuberID(string fnumber)
        {
            string cmdText = "select top 1 fid from PriceBill order by fid desc";
            DataTable table = DbService.getSQLTable(cmdText,"PriceBill");
            int fid = int.Parse(table.Rows[0]["fid"].ToString());
            return fid;
        }
    }
}